package com.swh.dao;

import org.springframework.dao.EmptyResultDataAccessException;

import java.util.List;
import java.util.Map;

/**
 * @Author: swh
 * @Description:
 * @Date: 2020/11/26 14:31
 * @Version: 1.0
 */

public class HomeDao extends BaseDao {
    public Map<String, Object> findHomeCount() {
        try {
            String sql = "SELECT " +
                    "    ( SELECT count( * ) FROM USER WHERE DATE_FORMAT( register_time,  '%Y-%m-%d' ) = CURDATE( ) ) userCount, " +
                    "    ( SELECT count( * ) FROM article WHERE DATE_FORMAT( publish_date, '%Y-%m-%d' ) = CURDATE( ) ) articleCount, " +
                    "    ( SELECT count( * ) FROM meeting WHERE DATE_FORMAT( publish_date, '%Y-%m-%d' ) = CURDATE( ) ) meetingCount";

            return jdbcTemplate.queryForMap(sql);
        } catch (EmptyResultDataAccessException exception) {
            return null;
        }
    }

    public List<Map<String, Object>> findHomeDetail() {
        String sql ="select  " +
                "(select  count(1) from user where DATE_FORMAT(register_time,'%Y-%m-%d') = ADDDATE(CURDATE(),-6)) day6, " +
                "(select  count(1) from user where DATE_FORMAT(register_time,'%Y-%m-%d') = ADDDATE(CURDATE(),-5)) day5, " +
                "(select  count(1) from user where DATE_FORMAT(register_time,'%Y-%m-%d') = ADDDATE(CURDATE(),-4)) day4, " +
                "(select  count(1) from user where DATE_FORMAT(register_time,'%Y-%m-%d') = ADDDATE(CURDATE(),-3)) day3, " +
                "(select  count(1) from user where DATE_FORMAT(register_time,'%Y-%m-%d') = ADDDATE(CURDATE(),-2)) day2, " +
                "(select  count(1) from user where DATE_FORMAT(register_time,'%Y-%m-%d') = ADDDATE(CURDATE(),-1)) day1, " +
                "(select  count(1) from user where DATE_FORMAT(register_time,'%Y-%m-%d') = ADDDATE(CURDATE(),-0)) today " +
                "union all " +
                "select  " +
                "(select  count(1) from article where DATE_FORMAT(publish_date,'%Y-%m-%d') = ADDDATE(CURDATE(),-6)) day6, " +
                "(select  count(1) from article where DATE_FORMAT(publish_date,'%Y-%m-%d') = ADDDATE(CURDATE(),-5)) day5, " +
                "(select  count(1) from article where DATE_FORMAT(publish_date,'%Y-%m-%d') = ADDDATE(CURDATE(),-4)) day4, " +
                "(select  count(1) from article where DATE_FORMAT(publish_date,'%Y-%m-%d') = ADDDATE(CURDATE(),-3)) day3, " +
                "(select  count(1) from article where DATE_FORMAT(publish_date,'%Y-%m-%d') = ADDDATE(CURDATE(),-2)) day2, " +
                "(select  count(1) from article where DATE_FORMAT(publish_date,'%Y-%m-%d') = ADDDATE(CURDATE(),-1)) day1, " +
                "(select  count(1) from article where DATE_FORMAT(publish_date,'%Y-%m-%d') = ADDDATE(CURDATE(),-0)) today " +
                " " +
                "union all " +
                "select  " +
                " " +
                "(select  count(1) from meeting where DATE_FORMAT(publish_date,'%Y-%m-%d') = ADDDATE(CURDATE(),-6)) day6, " +
                "(select  count(1) from meeting where DATE_FORMAT(publish_date,'%Y-%m-%d') = ADDDATE(CURDATE(),-5)) day5, " +
                "(select  count(1) from meeting where DATE_FORMAT(publish_date,'%Y-%m-%d') = ADDDATE(CURDATE(),-4)) day4, " +
                "(select  count(1) from meeting where DATE_FORMAT(publish_date,'%Y-%m-%d') = ADDDATE(CURDATE(),-3)) day3, " +
                "(select  count(1) from meeting where DATE_FORMAT(publish_date,'%Y-%m-%d') = ADDDATE(CURDATE(),-2)) day2, " +
                "(select  count(1) from meeting where DATE_FORMAT(publish_date,'%Y-%m-%d') = ADDDATE(CURDATE(),-1)) day1, " +
                "(select  count(1) from meeting where DATE_FORMAT(publish_date,'%Y-%m-%d') = ADDDATE(CURDATE(),-0)) today ";
        return jdbcTemplate.queryForList(sql);
    }
}

